import DbUtil
import GenLog
import SqlCmd_OA
import re
import xlwt
import SecretTool
from config import hr_config,api_config

su = SecretTool.SecertUtil()
dehruser, dehrpwd = su.decrypt(hr_config.userName, hr_config.passWord)
deapiuser, deapipwd = su.decrypt(api_config.userName, api_config.passWord)
# 映射表相关用户名密码加密
deapiuser2, deapipwd2 = su.decrypt(api_config.userName2, api_config.passWord2)

# 新建关于hr库的数据库对象
hrdb = DbUtil.DbUtil(hr_config.serverName, dehruser, dehrpwd, hr_config.dataBase)
hrdb.get_conn()

# 映射表相关数据库对象
mapdb = DbUtil.DbUtil(api_config.serverName2, deapiuser2, deapipwd2, api_config.dataBase2)
mapdb.get_conn()

# 新建关于API库的数据库对象
apidb = DbUtil.DbUtil(api_config.serverName, deapiuser, deapipwd, api_config.dataBase)
apidb.get_conn()

# 从HR库获取新入职员工 工号 姓名 一级部门 二级部门 职位 相关信息
# {'cWorkNo': '30729', 'cName': '滕介君', 'cPrimaryDept': '人事部', 'cDeptName': '人事部', 'cPositions': '招聘'}
rs_newcomer = hrdb.query(SqlCmd_OA.sqlcmd_NewComerToday)

# 计算总人数
total_count = rs_newcomer[1]

# 新入职员工生成器
def newerange():
    for tmp_list in rs_newcomer[0]:
        yield tmp_list

# 映射表中的HR_DEPTNAME列表化
rs_hrdeptname = mapdb.query('SELECT ID,HR_DEPTNAME,HR_POSITION FROM dbo.oa_autoops')
# print(rs_hrdeptname)


# 根据HR组织架构查找API组织架构
def QueryOAOrg(zPrimaryDept, zDeptName, zPositions):
    # 轮询HR deptname，例：信息部\硬件运维\系统运维组
    for tmp_hrdept in rs_hrdeptname[0]:
        # 匹配第一个\前字符串
        matchObj1 = re.match(r'^(\w+)', tmp_hrdept['HR_DEPTNAME'])
        group1 = matchObj1.group()
        # 匹配最后一个\后字符串
        matchObj2 = re.match(r'(.*)\\(\S+)$', tmp_hrdept['HR_DEPTNAME'])
        # 排除没有\的部门字符串情况
        if matchObj2 is None:
            group2 = group1
        else:
            group2 = matchObj2.group(2)
        if (group1 == zPrimaryDept and group2 == zDeptName) or (group1 + '（停用）' == zPrimaryDept and group2 == zDeptName) or (group1 + '（停用）' == zPrimaryDept and group2 + '（停用）' == zDeptName):
            list_hrdept = tmp_hrdept['HR_POSITION'].split('/')
            for tmp_list_hrdept in list_hrdept:
                if zPositions == tmp_list_hrdept:
                    # print(tmp_hrdept['ID'])
                    return tmp_hrdept['ID']
    else:
        # print('无department')
        return 0

# 初始化计数值
affcted_count = 0
unaffected_count = 0

# 初始化excel工作簿，定义第一行数据
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet('test', cell_overwrite_ok=True)
sheet.write(0, 0, '登录账号')
sheet.write(0, 1, '用户名称')
sheet.write(0, 2, '性别')
sheet.write(0, 3, '联系电话')
sheet.write(0, 4, '工作部门')
sheet.write(0, 5, '工作岗位')
sheet.write(0, 6, '访问权限')
sheet.write(0, 7, '直属领导')
sheet.write(0, 8, '兼职岗位')
id = 0
# HR新入职名单中取出
for list_tmp in newerange():
    # 初始化ID值
    DeptID = ''
    PostID = ''
    PostName = ''
    LeaderID = ''

    # 取出HRMS对应人员信息
    cWorkNo = str(list_tmp['cWorkNo'])
    cName = list_tmp['cName']
    cPrimaryDept = list_tmp['cPrimaryDept']
    cDeptName = list_tmp['cDeptName']
    cPosition = list_tmp['cPosition']
    # print(cPrimaryDept, cDeptName, cPosition) # 打印一级部门、二级部门及岗位

    # 获取hr_map_api表ID
    rs_QueryMapID = QueryOAOrg(cPrimaryDept, cDeptName, cPosition)

    # 未匹配人员信息进行输出
    if not rs_QueryMapID:
        GenLog.GenOALog(
            '''【工号：{}】未有对应部门，请将【一级部门：'{}' 末级部门：'{}' 岗位：'{}'】加入oa_autoops映射表对应组织架构中\n'''.format(cWorkNo, cPrimaryDept, cDeptName, cPosition))
        continue

    # 根据映射表映射出对应OA相关信息
    rs_queryoa = mapdb.query(SqlCmd_OA.sqlcmd_queryapi.format(rs_QueryMapID))
    # 获取oa组织架构信息
    oa_deptname = rs_queryoa[0][0]['OA_DEPTNAME']
    oa_position = rs_queryoa[0][0]['OA_POSITION']
    oa_leaderid = rs_queryoa[0][0]['API_LEADERID']
    oa_roles = rs_queryoa[0][0]['OA_ROLES']

    # 排除生产人员
    if oa_deptname == '无':
        unaffected_count += 1
        continue

    # 生成导入模板
    id += 1
    sheet.write(id, 0, cWorkNo)
    sheet.write(id, 1, cName)
    sheet.write(id, 4, oa_deptname)
    sheet.write(id, 5, oa_position)
    sheet.write(id, 6, oa_roles)
    sheet.write(id, 7, oa_leaderid)

    affcted_count += 1
book.save(r'.\OA账号导入.xls')
GenLog.GenOALog('---导入模板成功账号数量{}个---不用建OA账号的人员数量{}个---未建账号数量{}个---'.format(affcted_count, unaffected_count, total_count-affcted_count))